package product;

import java.awt.BorderLayout;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import javax.swing.JInternalFrame;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableCellRenderer;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableColumnModel;
import javax.swing.JPanel;
import javax.swing.JTextField;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.SwingConstants;

import java.awt.Dimension;

import javax.swing.DefaultComboBoxModel;

import java.awt.event.ActionListener;
import java.awt.event.ActionEvent;
import java.awt.event.ItemListener;
import java.awt.event.ItemEvent;

public class MemberHolyselect extends JInternalFrame {

	Connection conn;
	ProductMain3 pm;
	PreparedStatement pstmt;

	ResultSet rs;
	ResultSetMetaData rsmd;
	DefaultTableModel model;
	Vector<String> v;
	MemberHoly mHoly;
	DefaultComboBoxModel cmodel = new DefaultComboBoxModel<>();
	DefaultTableCellRenderer Center = new DefaultTableCellRenderer();
	TableColumnModel tcmSchedule;

	String team ="부서";
	String position ="직책";
	String name ="이름";

	private JScrollPane scrollPane;
	private JTable table;
	private JPanel panel;
	private JPanel panel_1;
	private JButton btnNewButton;
	private JComboBox comboteam;
	private JComboBox comboposition;
	private JComboBox comboname;
	private JButton btnNewButton_1;

	public MemberHolyselect(Connection conn, ProductMain3 pm, MemberHoly mh) {
		super("휴가 상세조회", true, true, true, false);
		this.conn=conn;
		this.pm=pm;
		this.mHoly=mh;
		setBounds(100, 100, 450, 300);
		getContentPane().add(getPanel(), BorderLayout.NORTH);
		getContentPane().add(getScrollPane(), BorderLayout.CENTER);

		serachname();

	}


	public void columnadd() {
		if (table!=null) table=null;

		try {    

			if(!team.equals("부서") && position.equals("직책") && name.equals("이름")){
				String sql = "select h.holyno, h.memberid as 사원번호, m.irum as 이름, to_char(h.holydate,'yyyy-mm-dd') as 휴가신청일, h.holytype as 휴가타입, h.info as 휴가정보 from holydata h inner join memberdata m on h.memberid=m.memberid where m.team=? and m.working=1 order by h.holyno";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, team);
			}else if(team.equals("부서") && !position.equals("직책") && name.equals("이름")){
				String sql = "select h.holyno, h.memberid as 사원번호, m.irum as 이름, to_char(h.holydate,'yyyy-mm-dd') as 휴가신청일, h.holytype as 휴가타입, h.info as 휴가정보 from holydata h inner join memberdata m on h.memberid=m.memberid where m.position=? and m.working=1 order by h.holyno";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, position);
			}else if(team.equals("부서") && position.equals("직책") && !name.equals("이름")){
				String sql = "select h.holyno, h.memberid as 사원번호, m.irum as 이름, to_char(h.holydate,'yyyy-mm-dd') as 휴가신청일, h.holytype as 휴가타입, h.info as 휴가정보 from holydata h inner join memberdata m on h.memberid=m.memberid where m.irum=? and m.working=1 order by h.holyno";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, name);
			}else if(!team.equals("부서") && !position.equals("직책") && name.equals("이름")){
				String sql = "select h.holyno, h.memberid as 사원번호, m.irum as 이름, to_char(h.holydate,'yyyy-mm-dd') as 휴가신청일, h.holytype as 휴가타입, h.info as 휴가정보 from holydata h inner join memberdata m on h.memberid=m.memberid where m.team=? and m.position=? and m.working=1 order by h.holyno";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, team);
				pstmt.setString(2, position);
			}else if(!team.equals("부서") && position.equals("직책") && !name.equals("이름")){
				String sql = "select h.holyno, h.memberid as 사원번호, m.irum as 이름, to_char(h.holydate,'yyyy-mm-dd') as 휴가신청일, h.holytype as 휴가타입, h.info as 휴가정보 from holydata h inner join memberdata m on h.memberid=m.memberid where m.team=? and m.irum=? and m.working=1 order by h.holyno";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, team);
				pstmt.setString(2, name);
			}else if(team.equals("부서") && !position.equals("직책") && !name.equals("이름")){
				String sql = "select h.holyno, h.memberid as 사원번호, m.irum as 이름, to_char(h.holydate,'yyyy-mm-dd') as 휴가신청일, h.holytype as 휴가타입, h.info as 휴가정보 from holydata h inner join memberdata m on h.memberid=m.memberid where m.position=? and m.irum=? and m.working=1 order by h.holyno";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, position);
				pstmt.setString(2, name);
			}else if(!team.equals("부서") && !position.equals("직책") && !name.equals("이름")){
				String sql = "select h.holyno, h.memberid as 사원번호, m.irum as 이름, to_char(h.holydate,'yyyy-mm-dd') as 휴가신청일, h.holytype as 휴가타입, h.info as 휴가정보 from memberdata m inner join holydata h on m.memberid=h.memberid where m.team=? and m.position=? and m.irum=? and m.working=1 order by h.holyno";
				pstmt.setString(1, team);
				pstmt.setString(2, position);
				pstmt.setString(3, name);

			}
			else if(team.equals("부서") && position.equals("직책") && name.equals("이름")){
				String sql = "select  h.holyno, h.memberid as 사원번호, m.irum as 이름, to_char(h.holydate,'yyyy-mm-dd') as 휴가신청일, h.holytype as 휴가타입, h.info as 휴가정보 from holydata h inner join memberdata m on h.memberid=m.memberid where m.working=1";
				pstmt = conn.prepareStatement(sql);
			}




			rs = pstmt.executeQuery();// 리절트셋에 대한 부가정보를 갖는 객체얻어오기
			rsmd = rs.getMetaData();// 컬럼명 테이블에 셋팅

			int colCount = rsmd.getColumnCount();
			String t[] = new String[colCount + 1];
			t[0] = "NO";

			for (int i = 1; i <= colCount; i++) {
				String colName = rsmd.getColumnName(i);
				t[i] = colName;
			}

			for(int i=0; i<=t.length-1; i++){
				System.out.println(t[i]);
			}

			model = new DefaultTableModel(t,0);
			scrollPane.setViewportView(getTable());

			int k=0; 
			while (rs.next()) {
				v= new Vector<String>();
				k=k+1;
				v.add(""+k);
				for (int j = 1; j <= colCount; j++) {
					v.add(rs.getString(j));
				}
				model.addRow(v);
			}
			
			tcmSchedule = table.getColumnModel();
			Center.setHorizontalAlignment(SwingConstants.CENTER);
			tcmSchedule.getColumn(0).setCellRenderer(Center);
			tcmSchedule.getColumn(1).setCellRenderer(Center);
			tcmSchedule.getColumn(2).setCellRenderer(Center);
			tcmSchedule.getColumn(4).setCellRenderer(Center);
			tcmSchedule.getColumn(5).setCellRenderer(Center);
			

			//			rs.close();
			//			pstmt.close();


		} catch (SQLException e) {
			e.printStackTrace();
		}

	}
	public void serachname(){
		try{
			String sql = "select irum from memberdata where working='1'";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			cmodel.addElement("이름");
			while(rs.next()){
				cmodel.addElement(rs.getString("irum"));
			}

			comboname.setModel(cmodel);

			//			rs.close();
			//			pstmt.close();

		}catch(Exception e){
			e.printStackTrace();
		}
	}
	public void serach(){
		try{
			cmodel = new DefaultComboBoxModel<>();

			if(!team.equals("부서") && position.equals("직책")){
				String sql = "select irum from memberdata where team=? and working='1'";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, team);

			}else if(team.equals("부서") && !position.equals("직책")){
				String sql = "select irum from memberdata where position=? and working='1'";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, position);

			}else if(!team.equals("부서") && !position.equals("직책")){
				String sql = "select irum from memberdata where team=? and position=? and working='1'";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, team);
				pstmt.setString(2, position);
			}else if(team.equals("부서") && position.equals("직책")){
				String sql = "select irum from memberdrata where working='1'";
				pstmt = conn.prepareStatement(sql);
			}

			rs = pstmt.executeQuery();
			cmodel.addElement("");
			while(rs.next()){
				cmodel.addElement(rs.getString(1));
			}
			comboname.setModel(cmodel);


			//			rs.close();
			//			pstmt.close();

			comboteam.setSelectedItem(0);
			comboposition.setSelectedItem(0);
			comboname.setSelectedItem(0);

		}catch(Exception e){
			e.printStackTrace();
		}


	}


	public void serachall() {
		if (table!=null) table=null;

		try {    

			String sql = "select * from holydata";
			pstmt = conn.prepareStatement(sql);


			rs = pstmt.executeQuery();// 리절트셋에 대한 부가정보를 갖는 객체얻어오기
			rsmd = rs.getMetaData();// 컬럼명 테이블에 셋팅

			int colCount = rsmd.getColumnCount();
			String t[] = new String[colCount + 1];
			t[0] = "NO";

			for (int i = 1; i <= colCount; i++) {
				String colName = rsmd.getColumnName(i);
				t[i] = colName;
			}

			for(int i=0; i<=t.length-1; i++){
				System.out.println(t[i]);
			}

			model = new DefaultTableModel(t,0);
			scrollPane.setViewportView(getTable());

			int k=0; 
			while (rs.next()) {
				v= new Vector<String>();
				k=k+1;
				v.add(""+k);
				for (int j = 1; j <= colCount; j++) {
					v.add(rs.getString(j));
				}
				model.addRow(v);
			}
			
			tcmSchedule = table.getColumnModel();
			Center.setHorizontalAlignment(SwingConstants.CENTER);
			tcmSchedule.getColumn(0).setCellRenderer(Center);
			tcmSchedule.getColumn(1).setCellRenderer(Center);
			tcmSchedule.getColumn(2).setCellRenderer(Center);
			tcmSchedule.getColumn(4).setCellRenderer(Center);
			tcmSchedule.getColumn(5).setCellRenderer(Center);
			
			//			rs.close();
			//			pstmt.close();


		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	private JScrollPane getScrollPane() {
		if (scrollPane == null) {
			scrollPane = new JScrollPane();
			scrollPane.setViewportView(getTable());
		}
		return scrollPane;
	}

	private JTable getTable() {
		if (table == null) {
			table = new JTable(model);
			table.addMouseListener(new MouseAdapter() {
				@Override
				public void mousePressed(MouseEvent e) {

					try{
						int row = -1;
						row = table.getSelectedRow();
						String k = (String) table.getValueAt(row, 2); // 사원번호
						String kk = (String) table.getValueAt(row, 4); // 날짜
						String kkk = (String) table.getValueAt(row, 5); // 타입
						String kkkk = (String) table.getValueAt(row, 6); // info
						String nal = kk.substring(0,10);
						if(kkk.equals("4") && !kkkk.equals("회사내규")){

							mHoly.reasoneditor(k,kk);
						}
						else if(mHoly.hr !=null){
							mHoly.hr.setVisible(false);
						}
					}catch(Exception ee){

					}

				}
			});
		}
		return table;
	}
	private JPanel getPanel() {
		if (panel == null) {
			panel = new JPanel();
			panel.setLayout(new BorderLayout(0, 0));
			panel.add(getPanel_1(), BorderLayout.CENTER);
		}
		return panel;
	}
	private JPanel getPanel_1() {
		if (panel_1 == null) {
			panel_1 = new JPanel();
			panel_1.add(getComboteam());
			panel_1.add(getComboposition());
			panel_1.add(getComboname());
			panel_1.add(getBtnNewButton());
			panel_1.add(getBtnNewButton_1());
		}
		return panel_1;
	}
	private JButton getBtnNewButton() {
		if (btnNewButton == null) {
			btnNewButton = new JButton("조회");
			btnNewButton.addActionListener(new ActionListener() {
				public void actionPerformed(ActionEvent e) {

					columnadd();


				}
			});
		}
		return btnNewButton;
	}
	private JComboBox getComboteam() {
		if (comboteam == null) {
			comboteam = new JComboBox();
			comboteam.addItemListener(new ItemListener() {
				public void itemStateChanged(ItemEvent e) {

					if(e.getStateChange() == e.SELECTED){ // 콤보박스 값이 선택되었을경우
						team = (String)(e.getItem()); 
					}
					serach();

				}
			});
			comboteam.setModel(new DefaultComboBoxModel(new String[] {"부서", "재경부", "기획부", "홍보부", "개발부", "마케팅부", "총무부"}));
			comboteam.setPreferredSize(new Dimension(70, 21));
		}
		return comboteam;
	}
	private JComboBox getComboposition() {
		if (comboposition == null) {
			comboposition = new JComboBox();
			comboposition.addItemListener(new ItemListener() {
				public void itemStateChanged(ItemEvent e) {

					if(e.getStateChange() == e.SELECTED){ // 콤보박스 값이 선택되었을경우
						position = (String)(e.getItem()); 
					}

					serach();
				}
			});
			comboposition.setModel(new DefaultComboBoxModel(new String[] {"직책", "사원", "대리", "부장", "과장"}));
			comboposition.setPreferredSize(new Dimension(70, 21));
		}
		return comboposition;
	}
	private JComboBox getComboname() {
		if (comboname == null) {
			comboname = new JComboBox(cmodel);
			comboname.addItemListener(new ItemListener() {
				public void itemStateChanged(ItemEvent e) {

					if(e.getStateChange() == e.SELECTED){ // 콤보박스 값이 선택되었을경우
						name = (String)(e.getItem()); 
					}

				}
			});
			comboname.setModel(new DefaultComboBoxModel(new String[] {""}));
			comboname.setPreferredSize(new Dimension(70, 21));
		}
		return comboname;
	}
	private JButton getBtnNewButton_1() {
		if (btnNewButton_1 == null) {
			btnNewButton_1 = new JButton("모두 조회");
			btnNewButton_1.addActionListener(new ActionListener() {
				public void actionPerformed(ActionEvent e) {

					serachall();

				}
			});
		}
		return btnNewButton_1;
	}
}
